﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using Dapper.Contrib.Extensions;
using Dapper;

namespace RecognitionForm
{
    public class ConnFactory
    {
        public static string ConnectionString = @"db";

        public static SQLiteConnection GetConnection()
        {
            return GetConnection(ConnectionString);
        }

        public static SQLiteConnection GetConnection(string ConnectionString)
        {
            SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder();
            sb.DataSource = ConnectionString;
            SQLiteConnection conn = new SQLiteConnection(sb.ToString());
            if (conn.State != ConnectionState.Closed) throw new InvalidOperationException("should be closed!");
            return conn;
        }
    }

    /// <summary> 
    /// 扩展方法 
    /// </summary> 
    public abstract class DapperDataAsync
    {
        #region 动态参数
        /// <summary>
        /// 动态参数
        /// </summary>
        public static DynamicParameters GetDynamicParameters()
        {
            return new DynamicParameters();
        }
        #endregion

        #region 查询系列
        /// <summary>
        /// 单个值返回值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static async Task<T> ExecuteScalarAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            using (var conn = ConnFactory.GetConnection())
            {
                await conn.OpenAsync();
                return await conn.ExecuteScalarAsync<T>(sql, param, transaction, commandTimeout, commandType);
            }
        }
        /// <summary>
        /// 强类型查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        public static async Task<IEnumerable<T>> QueryAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            using (var conn = ConnFactory.GetConnection())
            {
                await conn.OpenAsync();
                return await conn.QueryAsync<T>(sql, param, transaction, commandTimeout, commandType);
            }
        }

        /// <summary>
        /// 动态类型查询 | 多映射动态查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        public static async Task<IEnumerable<dynamic>> QueryAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            using (var conn = ConnFactory.GetConnection())
            {
                await conn.OpenAsync();
                return await conn.QueryAsync(sql, param, transaction, commandTimeout, commandType);
            }
        }
        #endregion

        #region 增删改系
        /// <summary>
        /// 增删改系
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static async Task<int> ExecuteAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            using (var conn = ConnFactory.GetConnection())
            {
                await conn.OpenAsync();
                return await conn.ExecuteAsync(sql, param, transaction, commandTimeout, commandType);
            }
        }
        #endregion

        #region 查询系
        /// <summary> 
        /// 获取Model-Key为int类型 
        /// </summary> 
        /// <typeparam name="T"></typeparam> 
        /// <param name="id"></param> 
        /// <param name="transaction"></param> 
        /// <param name="commandTimeout"></param> 
        /// <returns></returns> 
        public static async Task<T> GetAsync<T>(int id, IDbTransaction transaction = null, int? commandTimeout = null) where T : class, new()
        {
            using (var conn = ConnFactory.GetConnection())
            {
                return await conn.GetAsync<T>(id, transaction, commandTimeout);
            }
        }
        /// <summary> 
        /// 获取Model-Key为long类型 
        /// </summary> 
        /// <typeparam name="T"></typeparam> 
        /// <param name="id"></param> 
        /// <param name="transaction"></param> 
        /// <param name="commandTimeout"></param> 
        /// <returns></returns> 
        public static async Task<T> GetAsync<T>(long id, IDbTransaction transaction = null, int? commandTimeout = null) where T : class, new()
        {
            using (var conn = ConnFactory.GetConnection())
            {
                return await conn.GetAsync<T>(id, transaction, commandTimeout);
            }
        }
        /// <summary> 
        /// 获取Model-Key为Guid类型 
        /// </summary> 
        /// <typeparam name="T"></typeparam> 
        /// <param name="id"></param> 
        /// <param name="transaction"></param> 
        /// <param name="commandTimeout"></param> 
        /// <returns></returns> 
        public static async Task<T> GetAsync<T>(System.Guid id, IDbTransaction transaction = null, int? commandTimeout = null) where T : class, new()
        {
            using (var conn = ConnFactory.GetConnection())
            {
                return await conn.GetAsync<T>(id, transaction, commandTimeout);
            }
        }
        /// <summary> 
        /// 获取Model-Key为string类型 
        /// </summary> 
        /// <typeparam name="T"></typeparam> 
        /// <param name="id"></param> 
        /// <param name="transaction"></param> 
        /// <param name="commandTimeout"></param> 
        /// <returns></returns> 
        public static async Task<T> GetAsync<T>(string id, IDbTransaction transaction = null, int? commandTimeout = null) where T : class, new()
        {
            using (var conn = ConnFactory.GetConnection())
            {
                return await conn.GetAsync<T>(id, transaction, commandTimeout);
            }
        }
        /// <summary> 
        /// 获取Model集合（没有Where条件） 
        /// </summary> 
        /// <typeparam name="T"></typeparam> 
        /// <returns></returns> 
        public static async Task<IEnumerable<T>> GetAllAsync<T>() where T : class, new()
        {
            using (var conn = ConnFactory.GetConnection())
            {
                return await conn.GetAllAsync<T>();
            }
        }


        public static IEnumerable<T> GetAll<T>() where T : class, new()
        {
            using (var conn = ConnFactory.GetConnection())
            {
                return conn.GetAll<T>();
            }
        }
        #endregion

        #region 增删改
        /// <summary> 
        /// 插入一个Model 
        /// </summary> 
        /// <typeparam name="T"></typeparam> 
        /// <param name="model"></param> 
        /// <param name="transaction"></param> 
        /// <param name="commandTimeout"></param> 
        /// <param name="sqlAdapter"></param> 
        /// <returns></returns> 
        public static async Task<int> InsertAsync<T>(T model, IDbTransaction transaction = null, int? commandTimeout = null) where T : class, new()
        {
            using (var conn = ConnFactory.GetConnection())
            {
                return await conn.InsertAsync<T>(model, transaction, commandTimeout);
            }
        }

        public static async Task<bool> DeleteAsync<T>(T model, IDbTransaction transaction = null, int? commandTimeout = null) where T : class, new()
        {
            using (var conn = ConnFactory.GetConnection())
            {
                return await conn.DeleteAsync<T>(model, transaction, commandTimeout);
            }
        }


        /// <summary> 
        /// 更新一个Model 
        /// </summary> 
        /// <typeparam name="T"></typeparam> 
        /// <param name="connection"></param> 
        /// <param name="entityToUpdate"></param> 
        /// <param name="transaction"></param> 
        /// <param name="commandTimeout"></param> 
        /// <returns></returns> 
        public static async Task<T> UpdateAsync<T>(T model, IDbTransaction transaction = null, int? commandTimeout = null) where T : class, new()
        {
            using (var conn = ConnFactory.GetConnection())
            {
                bool b = await conn.UpdateAsync<T>(model, transaction, commandTimeout);
                if (b) { return model; }
                else { return null; }
            }
        }
        #endregion

        #region 分页查询
        /// <summary> 
        /// 分页查询(为什么不用out，请参考：http://www.cnblogs.com/dunitian/p/5556909.html) 
        /// </summary> 
        /// <param name="sql">查询语句</param> 
        /// <param name="p">动态参数</param> 
        /// <param name="sqlTotal">total语句</param> 
        /// <param name="p2">Total动态参数</param> 
        /// <returns></returns> 
        public static async Task<string> PageLoadAsync<T>(string sql, object p = null, string sqlTotal = "", object p2 = null)
        {
            var rows = await QueryAsync<T>(sql.ToString(), p);
            var total = rows.Count();
            if (!string.IsNullOrWhiteSpace(sqlTotal)) { total = await ExecuteScalarAsync<int>(sqlTotal, p2); }
            return new { rows = rows, total = total }.SerializeObject();
        }
        #endregion
    }
}
